<?php

// $Id$


require_once(TANGRA_MAIN_DIR.'interfaces/i_db_storable.class.php');


require_once('tcms_nomenclature_value.class.php');


class TCMS_Nomenclature_Value_DBC extends TCMS_Nomenclature_Value implements I_DB_Storable {
	public function save(DB_Connection $dbc) {
		$ret = false;

		if ($this->get_id()) {
			$ret = $this->update($dbc);
		} else {
			$ret = $this->insert($dbc);
			$this->set_id($ret);

		}

		return $ret;
	}


	public function load_by_id(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$ret = false;

		$sql = "select nomenclature, ".
						"value ".
					"from tcms_nomenclature_values where id = $id";
		$rez = $dbc->execute($sql);

		if (!$rez->is_eof()) {
			$rez_obj = $rez->fetch_object();

			$this->set_id($id);
			$this->set_nomenclature($rez_obj->NOMENCLATURE);
			$this->set_value(stripslashes($rez_obj->VALUE));

			$ret = $id;
		}

		return $ret;
	}


	protected function insert(DB_Connection $dbc) {
		$ret = false;

		$id = $dbc->generate_id('tcms_nomenclature_values_seq');
		$nomenclature = $this->get_nomenclature();
		$value = addslashes($this->get_value());

		if ($id) {
			$sql = "insert into tcms_nomenclature_values (id, ".
									      "nomenclature, ".
									      "value ".
									      ") ".
								"values ".
									      "($id,".
									      "$nomenclature, ".
									      "'$value' ".
									      ")";
			$dbc->execute($sql);
			$ret = $id;
		} else {
			throw new TE_Exception('tcms_nomenclature_values');
		}

		return $ret;
	}


	protected function update(DB_Connection $dbc) {
		$id = $this->get_id();

		$nomenclature = $this->get_nomenclature();
		$value = addslashes($this->get_value());

		$sql = "update tcms_nomenclature_values set ".
						"nomenclature = $nomenclature, ".
						"value = '$value' ".
					"where id = ".$id;

		$dbc->execute($sql);
		$ret = $id;

		return $ret;
	}


	public static function delete(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$sql = "delete from tcms_nomenclature_values where id = $id";
		$dbc->execute($sql);
	}


	public static function get_sql_for_grid($nomenclature_id) {
		tangra_if_not_int_throw_e($nomenclature_id);

		$sql = "select id, ".
						"value ".
					"from tcms_nomenclature_values where nomenclature = $nomenclature_id order by value asc";

		return $sql;
	}


	public static function	get_sql_count_for_grid($nomenclature_id) {
		tangra_if_not_int_throw_e($nomenclature_id);

		$sql = "select count(id) as total_rows from tcms_nomenclature_values where nomenclature = $nomenclature_id";

		return $sql;
	}


	public static function load_all_for_nomenclature(DB_Connection $dbc, $nom_id) {
		tangra_if_not_int_throw_e($nom_id);

		$ret = array();
		$sql = "select id tcms_nomenclature_values where nomenclature = $nom_id";
		$rez = $dbc->execute($sql);
		while ($rez_obj = $rez->fetch_object()) {
			$tmp = new TCMS_Nomenclature_Value_DBC();
			$tmp->load_by_id($dbc, $rez_obj->ID);
			$ret[] = clone $tmp;
		}

		return $ret;
	}


	public static function load_values_ol_map_for_nomenclature(DB_Connection $dbc, $nom_id) {
		$sql = "select id, name from tcms_nomenclature_values where nomenclature = $nom_id order by name asc";
		$rez = $dbc->execute($sql);

		$ret = array();
		$ids = array();

		while($rez_obj = $rez->fetch_object()) {
			array_push($ids, $rez_obj->ID);
			$ret['ol_map'][$rez_obj->ID] = $rez_obj->NAME;
		}

		if ($ids) {
			$ret['ids'] = $ids;
		}

		return $ret;
	}


	public static function get_sql_for_grid_nom($nom_id, $ci_id, $ciat_id, $text, $show) {
		tangra_if_not_int_throw_e($nom_id);

		$where = TCMS_Nomenclature_Value_DBC::prepare_where_clause($text, $show);
		if ($where) {
			$where = " and ".$where;
		}

		$sql = "select nom_val.id, ".
						"nom_val.value, ".
						"cia_nom.value as selected ".
					"from tcms_nomenclature_values nom_val left outer join  ".
							"tcms_cia_nomenclature cia_nom on (cia_nom.ci = $ci_id and cia_nom.ci_type_attr = $ciat_id and cia_nom.value = nom_val.id) ".
					"where nom_val.nomenclature = $nom_id ".$where.
					"order by nom_val.value asc";

		return $sql;
	}


	public static function	get_sql_count_for_grid_nom($nom_id, $ci_id, $ciat_id, $text, $show) {
		tangra_if_not_int_throw_e($nom_id);
		$where = TCMS_Nomenclature_Value_DBC::prepare_where_clause($text, $show);
		if ($where) {
			$where = " and ".$where;
		}

		if ($show) {
			$sql = "select count(nom_val.id) as total_rows ".
						"from tcms_nomenclature_values nom_val left outer join  ".
								"tcms_cia_nomenclature cia_nom on (cia_nom.ci = $ci_id and cia_nom.ci_type_attr = $ciat_id and cia_nom.value = nom_val.id) ".
						"where nom_val.nomenclature = $nom_id $where ";
		} else {
			$sql = "select count(id) as total_rows from tcms_nomenclature_values nom_val where nomenclature = $nom_id ".$where;
		}

		return $sql;
	}


	public static function exists_for_nomenclature(DB_Connection $dbc, $nom_id, $value) {
		tangra_if_not_int_throw_e($nom_id);
		tangra_if_not_int_throw_e($value);

		$sql = "select id from tcms_nomenclature_values where nomenclature = $nom_id and value = $value";
		$rez = $dbc->execute($sql);

		return !$rez->is_eod();
	}


	private static function remove_duplicate_asteriks($filter) {
		$ret = $filter;
		while(strpos($ret, '**') !== false) {
			$ret = str_replace('**', '*', $ret);
		}

		return $ret;
	}


	private static function prepare_where_clause($text = '', $show = false) {
		$text = TCMS_Nomenclature_Value_DBC::remove_duplicate_asteriks($text);

		if ($text || $show) {
			$where = "";
			if ($text) {
				if (strpos($text, '*') !== false) {
					$text = addslashes($text);
					$text = str_replace('*', '%', $text);
					$where .= "nom_val.value like '$text'";
				} else {
					$text = addslashes($text);
					$where .= "nom_val.value = '$text'";
				}
			}

			switch($show) {
				case 1:
					$where .= ($where ? ' and ' : '')." cia_nom.value is not NULL ";
					break;
				case 2:
					$where .= ($where ? ' and ' : '')." cia_nom.value is NULL ";
					break;
				case 0:
				default:
					break;
			}
		} else {
			$where = '';
		}

		return $where;
	}


	public static function count_for_nomenclature(DB_Connection $dbc, $nom_id) {
		tangra_if_not_int_throw_e($nom_id);
		$sql = "select count(id) as c from tcms_nomenclature_values where nomenclature = $nom_id";

		$rez = $dbc->execute($sql);
		$rez_obj = $rez->fetch_object();

		return $rez_obj->C;
	}
}
